﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Reflection;
using System.Data.SqlClient;
using Oracle.DataAccess.Client;
using System.Data.DataModel;

namespace System.Data.DataControl
{


    /// <summary>

    /// 拡張メソッド定義用Class

    /// </summary>

    public static class DbConnectorExtentions
    {

        /// <summary>

        /// OracleConnectionの場合に、BindByNameを使えるようにする

        /// </summary>

        /// <param name="command">Oraclecommand</param>

        /// <param name="isName">名前つきBindするかどうか</param>

        public static void SetBindByName(this IDbCommand Command, bool isName)
        {

            Type typeFileInfo = Command.GetType();

            PropertyInfo pi = typeFileInfo.GetProperty("BindByName");

            if (pi != null)
            {

                pi.SetValue(Command, isName, null);

            }

        }

    }

    public static class DataConnection
    {
        public static DbConnection Connection;

        public static DbTransaction Transaction;

        public static void DbConnectionFactory()
        {
            string dbProvider =
                System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;
            string connectionString =
                System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            switch (dbProvider)
            {
                case "System.Data.SqlClient":
                    Connection = new SqlConnection(connectionString);
                    break;
                case "Oracle.DataAccess.Client":
                    Connection = new OracleConnection(connectionString);
                    break;
                default:
                    Connection = new SqlConnection(connectionString);
                    break;
            }
        }

        public static void BeginTransaction()
        {
            if(Connection.State == ConnectionState.Open)
                Connection.Close();
            Connection.Open();
            Transaction = Connection.BeginTransaction();
        }

        public static void Commit()
        {
            Transaction.Commit();
            Connection.Close();
        }

        public static void RollBack()
        {
            Transaction.Rollback();
            Connection.Close();
        }


    }
    public class DbController : IDbController
    {
        #region IDbController メンバ

        protected string selectCommand;
        /// <summary>
        /// Select Query String
        /// </summary>
        string IDbController.SelectCommand
        {
            get
            {
                return this.selectCommand;
            }
            set
            {
                this.selectCommand = value;
            }
        }

        protected string selectAllCommand;
        /// <summary>
        /// Select All Query String
        /// </summary>
        string IDbController.SelectAllCommand
        {
            get
            {
                return this.selectAllCommand;
            }
            set
            {
                this.selectAllCommand = value;
            }
        }

        protected string updateCommand;
        /// <summary>
        /// Update Query String
        /// </summary>
        string IDbController.UpdateCommand
        {
            get
            {
                return updateCommand;
            }
            set
            {
                this.updateCommand = value;
            }
        }

        protected string insertCommand;
        /// <summary>
        /// Insert Query String
        /// </summary>
        string IDbController.InsertCommand
        {
            get
            {
                return this.insertCommand;
            }
            set
            {
                this.insertCommand = value;
            }
        }
        protected string deleteCommand;
        /// <summary>
        /// Delete Query String
        /// </summary>
        string IDbController.DeleteCommand
        {
            get
            {
                return deleteCommand;
            }
            set
            {
                this.deleteCommand = value;
            }
        }

        protected string errorMessage;
        public string ErrorMessage
        {
            get
            {
                return errorMessage;
            }
            set
            {
                this.errorMessage = value;
            }
        }


        /// <summary>
        /// DataCommand
        /// </summary>
        protected IDbCommand command;

        /// <summary>
        /// Get All List from Table
        /// </summary>
        /// <param name="type">Target Model Object</param>
        /// <returns>List of target model object</returns>
        public IEnumerable<object> GetAll(object obj)
        {
            this.command.CommandText = this.selectAllCommand;
            return SetIEnumerableFromReader(obj);
        }   


        /// <summary>
        /// 主キーでデータ取得
        /// </summary>
        /// <param name="obj">対象クラスのインスタンス</param>
        /// <param name="parameterProperties">抽出対象のプロパティのコレクション</param>
        /// <returns></returns>
        public virtual IEnumerable<object> GetData(object obj, string[] parameterProperties)
        {
            foreach (string parameter in parameterProperties)
            {
                this.SetParameter(obj.GetType().GetProperty(parameter), obj);
            }
            this.command.CommandText = this.selectCommand;
            return SetIEnumerableFromReader(obj);
        }

        /// <summary>
        /// 複数パラメータを持ったデータ抽出
        /// </summary>
        /// <param name="obj">対象クラスのインスタンス</param>
        /// <param name="parameterProperties">抽出対象のプロパティのコレクション</param>
        /// <returns></returns>
        public virtual IEnumerable<object> GetData(object obj)
        {
            this.command.CommandText = this.selectCommand;

            return SetIEnumerableFromReader(obj);

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        private IEnumerable<object> SetIEnumerableFromReader(object type)
        {

            List<object> list = new List<object>();
     

            if (command.Connection.State != ConnectionState.Open)
            {
                this.command.Connection.Open();
            }  
            DbDataReader reader = this.GetDataReader();

            while (reader.Read())
            {
                Type t = type.GetType();
                var data = Activator.CreateInstance(type.GetType());

                foreach (PropertyInfo property in t.GetProperties())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        if (reader.GetName(i).ToLower() == property.Name.ToLower())
                        {
                            property.SetValue(data, reader[i], null);
                            break;
                        }
                    }

                }
                list.Add(data);
            }

            //トランザクションが設定されていなければClose
            if(this.command.Transaction == null)
                this.command.Connection.Close();


            this.command.Parameters.Clear();
 

            return list;
        }

        public int Insert(object obj, string[] primaryKey)
        {
            this.SetParameters(obj, primaryKey);
            this.command.CommandText = this.insertCommand;

            return this.ExecQuery();
            
        }

        public int Update(object obj)
        {
            string[] primarykey = {};
            this.SetParameters(obj, primarykey);
            this.command.CommandText = this.updateCommand;

            return this.ExecQuery();
        }

        public int Delete(object obj, string primaryKey)
        {
            this.SetParameter(obj.GetType().GetProperty(primaryKey), obj);
            this.command.CommandText = this.deleteCommand;

            return this.ExecQuery();
        }

        public virtual int CreateTable(bool dropTable, string className, List<PropertySet> properties, string filePath)
        {
            //ToDo Try Catch
            string fullPath = filePath + "SQL\\";
            string file = fullPath + className + ".sql";
            System.IO.Directory.CreateDirectory(fullPath);

            System.IO.File.WriteAllText(file, this.command.CommandText);
            return this.ExecQuery();
        }


        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        protected int ExecQuery()
        {
            int count = 0;
            if (command.Connection.State != ConnectionState.Open)
            {
                this.command.Connection.Open();
            }            
            count = this.command.ExecuteNonQuery();


            this.command.Parameters.Clear();

            return count;
        }

        /// <summary>
        /// Set Database Command
        /// </summary>
        /// <param name="connectionString"></param>
        public virtual void SetCommand()
        {
            
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        protected virtual DbDataReader GetDataReader()
        {
            DbDataReader reader = null;
            return reader;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public virtual void SetParameters(object obj, string[] primarykey)
        {
        }


        public virtual void SetParameter(PropertyInfo property, object obj)
        {
        }

        public virtual void SetParameter(string parameterName, object parameterValue)
        {
        }

        public void SetTransaction()
        {
           this.command.Transaction = DataConnection.Transaction;
        }

        #endregion

        #region Factory
        //Connectionのファクトリ

        /// <summary>
        /// ConnectionStringのプロバイダ名からどのDBコントローラーを使うか決定して、
        /// そのインスタンスを生成する。
        /// </summary>
        /// <returns></returns>
        public static IDbController DbControllerFactory()
        {
            string dbProvider =
                System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;

            
            switch (dbProvider)
            {
                case "System.Data.SqlClient":
                    return new SqlController();
                case "Oracle.DataAccess.Client":
                    return new OracleController();
                default:
                    return new SqlController();
            }
        }

        #endregion

        #region Constructor
        public DbController()
        {
        }


        public DbController(string connectionString)
        {
        }


        #endregion




        #region IDbController メンバ


        IDbCommand IDbController.Command
        {
            get
            {
                return this.command;
            }
            set
            {
                this.command = value;
            }
        }

        #endregion



    }
}
